﻿using Dapper;
using Student.Common;
using Student.Model;

namespace Student.DAL
{
    public class UserDAO
    {
        public UserData? GetUser(string userName)
        {
            var sql = "SELECT * FROM UserData WHERE UserName = @UserName";
            using var conn = ConnectionFactory.CreateConnection() ;
            var model = conn.QueryFirstOrDefault<UserData>(sql, new { UserName = userName });
            return model;
        }

        public UserData? GetUser(int userId)
        {
            var sql = "SELECT * FROM UserData WHERE ID = @UserID";
            using var conn = ConnectionFactory.CreateConnection();
            var model = conn.QueryFirstOrDefault<UserData>(sql, new { UserID = userId });
            return model;
        }

        public int Insert(UserData user)
        {
            var sql = "INSERT INTO UserData(UserName, Password) VALUES (@UserName, @Password);" +
                      "SELECT CAST(SCOPE_IDENTITY() AS int)";
            
            using var conn = ConnectionFactory.CreateConnection();

            conn.Open();
            user.Id = conn.Query<int>(sql, user).Single();                        
            conn.Close();

            return 1;
        }

        public List<UserData> GetList()
        {
            var sql = "SELECT * FROM UserData";
            using (var conn = ConnectionFactory.CreateConnection())
            {
                var q = conn.Query<UserData>(sql);
                return q.ToList();
            }
        }
    }
}
